#!/usr/bin/env python3
import psycopg2

# 数据库连接
def get_connection():
    return psycopg2.connect(
        host="awsprddbs4836.shared.sydney.edu.au",
        port=5432,
        database="y25s1c9120_ruma0741",
        user="y25s1c9120_ruma0741",
        password="032327"
    )

# 1. 登录验证
def checkLogin(login, password):
    conn = get_connection()
    cur = conn.cursor()
    cur.execute("SELECT UserName, FirstName, LastName FROM Salesperson WHERE UserName = %s AND Password = %s;", (login, password))
    result = cur.fetchone()
    cur.close()
    conn.close()
    return result

# 2. 获取车辆销售摘要（使用 SQL 存储函数）
def getCarSalesSummary():
    conn = get_connection()
    cur = conn.cursor()
    cur.execute("SELECT * FROM GetCarSalesSummary();")
    rows = cur.fetchall()
    cur.close()
    conn.close()
    return rows

# 3. 模糊查找车辆销售记录（使用 SQL 存储函数）
def findCarSales(search):
    conn = get_connection()
    cur = conn.cursor()
    cur.execute("SELECT * FROM FindCarSales(%s);", (search,))
    rows = cur.fetchall()
    cur.close()
    conn.close()
    return rows

# 4. 添加新车辆销售记录（使用 SQL 存储函数）
def addCarSale(make, model, builtyear, odometer, price):
    conn = get_connection()
    cur = conn.cursor()
    try:
        cur.execute("SELECT AddCarSale(%s, %s, %s, %s, %s);", (make, model, builtyear, odometer, price))
        conn.commit()
        success = True
    except Exception:
        conn.rollback()
        success = False
    cur.close()
    conn.close()
    return success

# 5. 更新车辆销售信息（使用 SQL 存储函数 + 查 ID 也用 SQL 存储函数）
def updateCarSale(carsaleid, customer_name, salesperson_name, saledate):
    conn = get_connection()
    cur = conn.cursor()
    try:
        # 用函数查 BuyerID
        cur.execute("SELECT GetCustomerIDByName(%s);", (customer_name,))
        buyer = cur.fetchone()
        if not buyer:
            return False
        buyer_id = buyer[0]

        # 用函数查 SalespersonID
        cur.execute("SELECT GetSalespersonIDByName(%s);", (salesperson_name,))
        seller = cur.fetchone()
        if not seller:
            return False
        salesperson_id = seller[0]

        # 更新销售信息
        cur.execute("SELECT UpdateCarSale(%s, %s, %s, %s);", (carsaleid, buyer_id, salesperson_id, saledate))
        conn.commit()
        success = True
    except Exception:
        conn.rollback()
        success = False
    cur.close()
    conn.close()
    return success
